from urllib.error import HTTPError


def new():
    # 创建
    from openpyxl import Workbook

    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    # 方式一：插入到最后(default)
    ws1 = wb.create_sheet("Mysheet")
    # 方式二：插入到最开始的位置
    ws2 = wb.create_sheet("Mysheet", 0)
    # 保存
    wb.save('new.xlsx')


def open():
    # 打开已有
    from openpyxl import load_workbook

    wb = load_workbook('C:/Users/Administrator/Desktop/直播源.xlsx')
    # 选择已有表
    # sheet 名称可以作为 key 进行索引
    ws1 = wb.active
    ws3 = wb["Sheet1"]
    ws4 = wb.get_sheet_by_name("Sheet1")
    print(ws1 is ws3 is ws4)


def show_sheet():
    # 查看表名
    from openpyxl import load_workbook

    wb = load_workbook('data01.xlsx')
    print(wb.sheetnames)
    for s in wb:
        print(s.title)


def get_one_value():
    # 获取值
    from openpyxl import load_workbook

    wb = load_workbook('data01.xlsx')
    ws = wb.active
    v1 = ws['B3']
    v2 = ws.cell(row=4, column=3)
    print(v1.value, v2.value)


def get_many_value():
    # 获取值
    from openpyxl import load_workbook

    wb = load_workbook('C:/Users/Administrator/Desktop/直播源.xlsx')
    ws = wb.active
    # 通过切片
    cell_range = ws['A1':'C2']
    # 通过行(列)
    colC = ws['C']
    colCD = ws['C':'D']
    row10 = ws[10]
    row_range = ws[5:10]
    print(cell_range)
    print(colC)
    print(colCD)
    print(row10)
    print(row_range)
    # 通过指定范围(行 → 行)3行3列
    for row in ws.iter_rows(min_row=2, max_row=5, max_col=3):
        for cell in row:
            print(cell.value)
        # 通过指定范围(列 → 列)
        for row in ws.iter_rows(min_col=3, max_col=5, min_row=1, max_row=5):
            for cell in row:
                print(cell.value)
        # 遍历所有 方法一
    print(tuple(ws.rows))
    # 遍历所有 方法二
    print(tuple(ws.columns))


def get_many_value2():
    # 获取值
    from openpyxl import load_workbook
    import ssl
    import urllib
    from urllib.request import urlopen
    from urllib.request import Request

    wb = load_workbook('C:/Users/Administrator/Desktop/直播源.xlsx')
    ws = wb.active
    # sheet.rows 为生成器, 里面是每一行的数据，每一行又由一个tuple包裹。
    # sheet.columns 类似，不过里面是每个tuple是每一列的单元格。
    # 因为按行，所以返回A1,B1,C1这样的顺序
    for row in ws.rows:
        # print(row.row_idx )
        # print(row.index)
        live_link = ''
        for cell in row:
            # if cell.value is not None:
            # print(cell.value, sep=',', end='\t')
            # print(dir(cell))
            # print(cell.row)

            if cell.col_idx == 2 and cell.value.find('edge-hls.sacdnssedge') > 0:
                # print(dir(cell))
                request = Request(cell.value)
                # response = urlopen(request)
                # print(response.read().decode())

                # 忽略SSL安全认证
                context = ssl._create_unverified_context()
                # 添加到context参数里
                try:
                    response = urllib.request.urlopen(request, context=context)
                    # print(response.read().decode())
                    response_array = response.read().decode().split('\n')
                    live_link = response_array[4]

                    # print(f'live_link===={response_array[4]}')
                except HTTPError as he:
                    pass
                    # print(he)
            if   live_link != '':
                if cell.col_idx == 4:
                # pass
                # print(f'将给{cell.row}行{cell.col_idx}列写入：{live_link}')
                    ws.cell(cell.row, cell.col_idx).value = live_link
                ws.cell(cell.row, 5).value = '开播'
            else:
                if cell.col_idx == 4:
                    ws.cell(cell.row, cell.col_idx).value = ''
                ws.cell(cell.row, 5).value = '未开播'
        # print(sep='\n')
    wb.save('C:/Users/Administrator/Desktop/直播源.xlsx')
    # A1, A2, A3 这样的顺序
    # for column in ws.columns:
    #     for cell in column:
    #         print(cell.value)


def get_num():
    # 获取值
    from openpyxl import load_workbook

    wb = load_workbook('data01.xlsx')
    ws = wb.active
    # 获得最大列和最大行
    print(ws.max_row)
    print(ws.max_column)


def remove_sheet():
    # 获取值
    from openpyxl import load_workbook

    wb = load_workbook('data01.xlsx')
    wb.remove('Sheet1')
    del wb['Sheet1']


def set_value_style():
    from openpyxl.styles import Font, colors, Alignment

    bold_itatic_24_font = Font(name=' 微 软 雅 黑 ', size=34, italic=True, color=colors.BLUE,
                               bold=True)
    # 创建
    from openpyxl import Workbook

    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    # 方式一：插入到最后(default)
    ws = wb.create_sheet("Mysheet")
    ws['B2'] = 'Hello!'
    ws['B2'].font = bold_itatic_24_font
    # 保存
    wb.save('new2.xlsx')


def set_value():
    # 创建
    from openpyxl import Workbook

    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    data = ['养老', '医疗', '公积金']
    for i, d in enumerate(data):
        ws.cell(i + 1, 1).value = d
    wb.save('new3.xlsx')


def set_style2():
    from openpyxl.styles import Font, colors, Alignment
    # 创建
    from openpyxl import Workbook

    16
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    ws.row_dimensions[1].height = 40
    ws.column_dimensions['C'].width = 40
    data = ['养老', '医疗', '公积金']
    for i, d in enumerate(data):
        ws.cell(i + 1, 1).value = d
    ws.cell(i + 1, 1).alignment = Alignment(horizontal='center', vertical='center')
    wb.save('new4.xlsx')


def set_value2():
    # 创建
    from openpyxl import Workbook
    from datetime import date
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    rows = [
        ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
        [date(2020, 12, 1), 40, 30, 25],
        [date(2020, 12, 2), 40, 25, 30],
        [date(2020, 12, 3), 50, 30, 45],
        [date(2020, 12, 4), 30, 25, 40],
        [date(2020, 12, 5), 25, 35, 30],
        [date(2020, 12, 6), 20, 40, 35],
    ]
    for row in rows:
        ws.append(row)
    wb.save('new6.xlsx')


def set_merge():
    from openpyxl.styles import Alignment
    # 创建
    from openpyxl import Workbook

    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    ws.merge_cells('A1:c1')
    ws.merge_cells('D2:E5')
    ws['A1'] = '横向合并'
    ws.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
    ws['D2'] = '综合合并'
    ws['D2'].alignment = Alignment(horizontal='center', vertical='center')
    wb.save('new5.xlsx')


# sheet.unmerge_cells('A1:C3')
def set_img():
    # 创建
    from openpyxl.chart import LineChart, Reference
    from openpyxl import Workbook
    from datetime import date
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    rows = [
        ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
        [date(2020, 12, 1), 40, 30, 25],
        [date(2020, 12, 2), 40, 25, 30],
        [date(2020, 12, 3), 50, 30, 45],
        [date(2020, 12, 4), 30, 25, 40],
        [date(2020, 12, 5), 25, 35, 30],
        [date(2020, 12, 6), 20, 40, 35],
    ]
    for row in rows:
        ws.append(row)
    c1 = LineChart()
    c1.title = "Line Chart"
    # c1.style = 2
    c1.y_axis.title = 'Size'
    c1.x_axis.title = 'Test Number'
    # min_row 第几行进行分类 min_col 第几列开始 max_col 取到第几列数据 max_row 取到第几行数据
    data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
    # titles_from_data 是否启用标题
    c1.add_data(data, titles_from_data=True)
    19
    ws.add_chart(c1, 'A9')
    wb.save('new7.xlsx')


def set_img2():
    from openpyxl.chart import PieChart, Reference
    from openpyxl import Workbook

    data = [
        ['名称', '数值'],
        ['苹果', 50],
        ['草莓', 30],
        ['椰子', 10],
        ['荔枝', 40],
    ]
    wb = Workbook()
    ws = wb.active
    for row in data:
        ws.append(row)
    pie = PieChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=5)
    data = Reference(ws, min_col=2, min_row=1, max_row=5)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "Pies sold by category"
    # Cut the first slice out of the pie
    # slice = DataPoint(idx=0, explosion=20)
    # pie.series[0].data_points = [slice]
    ws.add_chart(pie, "D1")
    wb.save('new8.xlsx')


def set_img3():
    from openpyxl import Workbook

    from openpyxl.chart import BarChart, Reference

    wb = Workbook()
    ws = wb.active
    rows = [
        ('Number', 'Batch 1', 'Batch 2'),
        (2, 10, 30),
        (3, 40, 60),
        (4, 50, 70),
        (5, 20, 10),
        (6, 10, 40),
        (7, 50, 30),
    ]
    for row in rows:
        ws.append(row)
    chart1 = BarChart()
    chart1.type = "col"
    chart1.style = 10
    chart1.title = "Bar Chart"

    chart1.y_axis.title = 'Testnumber'
    chart1.x_axis.title = 'Samplelength(mm)'
    data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
    cats = Reference(ws, min_col=1, min_row=2, max_row=7)
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cats)
    chart1.shape = 4
    ws.add_chart(chart1, "A10")
    wb.save('new9.xlsx')


if __name__ == '__main__':
    get_many_value2()
